﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading;
using System.Data;
using System.Threading.Tasks;
using System.Collections.Concurrent;


class CFlightDB
{
    float m_fMaxRate;
    String m_sDir;
    bool m_bWithManaged;
    SqlConnection m_SqlConnection;
    CXMLReader m_XMLReader;
    SqlCommand m_SQLCommand;
    CConnectionManager m_ConnectionManager;

    public CFlightDB(String sConnString)
    {
        m_ConnectionManager = new CConnectionManager();
        Console.WriteLine("Connecting to data base...");
        m_SqlConnection = new SqlConnection(sConnString);
        m_SQLCommand = new SqlCommand("", m_SqlConnection);
        m_bWithManaged = false;
    }

    public void SetManaged(bool bWithManaged)
    {
        m_bWithManaged = bWithManaged;
    }

    public void AddFlightsToDB(String sDir, String sXMLFile)
    {
        m_sDir = sDir;
        m_SqlConnection.Open();
        m_XMLReader = new CXMLReader(sXMLFile);

        int nPlaneId = GetPlaneId();
        AddFlightData(nPlaneId);

        m_SqlConnection.Close();
    }

    private bool IsFlightExists()
    {
        m_SQLCommand.CommandText = String.Format("SELECT Id FROM Flight WHERE Name = '{0}'", m_XMLReader.GetFlightName());
        SqlDataReader SQLReader = m_SQLCommand.ExecuteReader();
        bool IsExists = SQLReader.Read();
        SQLReader.Close();
        return IsExists;
    }

    private int GetPlaneId()
    {
        String sPlaneName = m_XMLReader.GetPlaneName();
        int nNumberPlane = m_XMLReader.GetNumberPlane();
        m_SQLCommand.CommandText = String.Format("SELECT Id FROM Planes WHERE  Number = {0}",  nNumberPlane);
        SqlDataReader SQLReader = m_SQLCommand.ExecuteReader();

        if (SQLReader.Read())
        {
            int nId = SQLReader.GetInt32(0);
            SQLReader.Close();
            return nId;
        }
        else
        {
            SQLReader.Close();
            m_SQLCommand.CommandText = String.Format("INSERT Planes VALUES('{0}', {1})", sPlaneName, nNumberPlane);
            m_SQLCommand.ExecuteNonQuery();
            SQLReader.Close();
            return GetPlaneId();
        }
    }

    private void AddFlightData(int nPlaneId)
    {
        Object a = new Object();
        lock (a)
        {
            if (!IsFlightExists())
            {
                m_SQLCommand.CommandText = String.Format("SELECT MAX(Number) FROM Flight");
                SqlDataReader SQLReader = m_SQLCommand.ExecuteReader();
                SQLReader.Read();
                int nNumber = SQLReader.GetInt32(0) + 1;
                SQLReader.Close();

                String sStartDate = m_XMLReader.GetStartDate().ToString("yyyy-MM-dd hh:mm:ss");
                sStartDate = sStartDate.Replace('.', '-');
                String sStopDate = m_XMLReader.GetStopDate().ToString("yyyy-MM-dd hh:mm:ss");
                sStopDate = sStopDate.Replace('.', '-');
                m_SQLCommand.CommandText = String.Format("INSERT Flight VALUES('{0}', {1}, {2}, '{3}', '{4}')", m_XMLReader.GetFlightName()
                                                                                                              , nPlaneId
                                                                                                              , nNumber
                                                                                                              , sStartDate
                                                                                                              , sStopDate);

                m_SQLCommand.ExecuteNonQuery();
            }
        }


        int nFlightId = GetFlightId();
        AddPatameters(nFlightId);
        Console.WriteLine("\n\nFlight is added");

    }

    private int GetFlightId()
    {
        m_SQLCommand.CommandText = String.Format("SELECT Id FROM Flight WHERE Name = '{0}'", m_XMLReader.GetFlightName());

        SqlDataReader SQLReader = m_SQLCommand.ExecuteReader();
        int nId = 0;
        if (SQLReader.Read())
            nId = SQLReader.GetInt32(0);
        SQLReader.Close();
        return nId;
    }

    private void AddPatameters(int nFlightId)
    {
        Console.WriteLine("\n\nParameters are adding. Wait...");

        List<SParam> ParamsList = m_XMLReader.GetParamsList();
        List<SFile> FileList = m_XMLReader.GetBinaryFiles();

        List<CBinaryReader> BinaryReaderList = new List<CBinaryReader>();
        foreach (SFile File in FileList)
            BinaryReaderList.Add(new CBinaryReader(m_sDir + File.m_sFileName, File.m_fRate));

        //Нахождение максимальной частоты
        foreach (CBinaryReader BinReader in BinaryReaderList)
            if (m_fMaxRate < BinReader.m_fRate)
                m_fMaxRate = BinReader.m_fRate;

        foreach (SParam Param in ParamsList)
        {
            int nIdParam = GetParamId(Param.m_sName, Param.m_fRate);
            
            foreach (CBinaryReader BinReader in BinaryReaderList)
                if (BinReader.m_fRate == Param.m_fRate)
                {
                   // float fRegTime = 0;
                     //int nTact = 0;
                    List<float> Values = BinReader.GetListOfValues(Param.m_nOffset);

                    ParallelOptions Options = new ParallelOptions();
                    Options.MaxDegreeOfParallelism = 8;
                    try
                    {
                        Parallel.ForEach(Partitioner.Create(0, Values.Count), range =>
                        {
                            for (int nInd = range.Item1; nInd < range.Item2; nInd++)
                            {
                                float fVal = Values[nInd];
                                float fRegTime = (float)1.0 / Param.m_fRate * nInd;
                                int nK = (int)(m_fMaxRate / Param.m_fRate);
                                int nTact = nInd * nK;
                                int nThreadId = Thread.CurrentThread.ManagedThreadId;
                                SqlCommand SQLCommand = m_ConnectionManager.GetSqlCommand(nThreadId);
                                AddDataToRawValue(fVal, nTact, fRegTime, nFlightId, nIdParam, SQLCommand);
                            }
                        }
                        );
                    }
                    catch (AggregateException e)
                    {
                        Console.WriteLine("Parallel.ForEach has thrown an exception. THIS WAS NOT EXPECTED.\n{0}", e);
                    }
                    m_ConnectionManager.CloseConnections(); 
                }
        }
    }

    private int GetParamId(String sName, float fRate)
    {
        String sRate = fRate.ToString().Replace(",", ".");
        m_SQLCommand.CommandText = String.Format("SELECT Id FROM Parameter WHERE Name='{0}' AND Freq = {1}", sName, sRate);
        SqlDataReader SQLReader = m_SQLCommand.ExecuteReader();

        if (SQLReader.Read())
        {
            int nId = SQLReader.GetInt32(0);
            SQLReader.Close();
            return nId;
        }
        SQLReader.Close();

        m_SQLCommand.CommandText = String.Format("INSERT Parameter VALUES('{0}', {1})", sName, sRate);
        m_SQLCommand.ExecuteNonQuery();
        SQLReader.Close();
        return GetParamId(sName, fRate);
    }

    private void AddDataToRawValue(float fValue,
                                   int nTact,
                                   float fRegTime,
                                   int nFlightId,
                                   int nParamId,
                                   SqlCommand SQLCommand)
    {
        String sValue = fValue.ToString().Replace(",", ".");
        String sRegTime = fRegTime.ToString().Replace(",", ".");
        fValue = (float)Math.Round(fValue, 10);
        if (m_bWithManaged)
        {
            SQLCommand.CommandText = String.Format("SELECT Id FROM RawValue WHERE Val={0} AND Tact={1} AND RegTime = {2} AND FlightId={3} AND ParamId={4}"
                                                                                                         , sValue
                                                                                                         , nTact
                                                                                                         , fRegTime
                                                                                                         , nFlightId
                                                                                                         , nParamId);
            SqlDataReader SQLReader = SQLReader = SQLCommand.ExecuteReader();
            if (SQLReader.Read())
            {
                SQLReader.Close();
                return;
            }
            SQLReader.Close();
        }
        try
        {
            if ((!float.IsNaN(fValue)) && (!float.IsInfinity(fValue)))
            {
                SQLCommand.CommandText = String.Format("INSERT RawValue VALUES(@Val, @nTact, @fRegTime, @nFlightId, @nParamId)");
                SQLCommand.Parameters.AddWithValue("@Val", fValue);
            }
            else
            {
                SQLCommand.CommandText = String.Format("INSERT INTO RawValue (Tact, regTime, FlightId, ParamId) VALUES (@nTact, @fRegTime, @nFlightId, @nParamId)");
            }
            SQLCommand.Parameters.AddWithValue("@nTact", nTact);
            SQLCommand.Parameters.AddWithValue("@fRegTime", fRegTime);
            SQLCommand.Parameters.AddWithValue("@nFlightId", nFlightId);
            SQLCommand.Parameters.AddWithValue("@nParamId", nParamId);
            //Console.WriteLine(nParamId.ToString() + "\n");
            SQLCommand.ExecuteNonQuery();
            SQLCommand.Parameters.Clear();
        }
        catch (SqlException e)
        {
            Console.WriteLine(fValue.ToString());
        }
    }

}

